package com.lovemanai.test;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

import com.lovemanai.dao.HibernateSessionFactory;
import com.lovemanai.entity.City;
import com.lovemanai.entity.User;

public class Demo1 {
	
	@Test
	public void test1() {
		String hql = "from User";
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.createQuery(hql);
		
		@SuppressWarnings("unchecked")
		List<User> users = query.list();
		
		for (User user : users) {
			System.out.println(user.getUname() + " , " + user.getCity().getName());
		}
		
		if(session != null) session.close();
	}
	
	@Test
	public void test2() {
		String hql = "select u.uname,u.age,u.city.name from User u where u.age > 20";
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.createQuery(hql);
		
		@SuppressWarnings("unchecked")
		List<Object[]> users = query.list();
		
		for (Object[] user : users) {
			System.out.println(user[0] + " , " + user[1] + " , " + user[2]);
		}
		
		if(session != null) session.close();
	}

	
	/*
	 * 参数命名查询
	 */
	@Test
	public void test4() {
		String hql = "select u.uname,u.age,u.city.name from User u "
				+ "where u.age > :age and u.city.name like :cityname";
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.createQuery(hql);
		
		query.setInteger("age", 20);
		query.setString("cityname", "%京");
		
		@SuppressWarnings("unchecked")
		List<Object[]> users = query.list();
		
		for (Object[] user : users) {
			System.out.println(user[0] + " , " + user[1] + " , " + user[2]);
		}
		if(session != null) session.close();
	}
	
	@Test
	public void test5() {
		String hql = "select count(c) from City c";
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.createQuery(hql);
		
		Object count = query.uniqueResult();
		
		System.out.println(count);
		if(session != null) session.close();
	}
	
	/*
	 * 使用组函数
	 */
	@Test
	public void test6() {
		String hql = "select u.city,max(u.age) from User u group by u.city";
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.createQuery(hql);
		
		List<Object[]> list = query.list();
		
		for (Object[] object : list) {
			System.out.println(((City)object[0]).getName() + "," + object[1]);
		}
		if(session != null) session.close();
		
	}
	
	@Test
	public void test7() {
		String hql = "select u.city,max(u.age),avg(u.age) from User u group by u.city";
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.createQuery(hql);
		
		List<Object[]> list = query.list();
		
		for (Object[] object : list) {
			System.out.println(((City)object[0]).getName() + "," + object[1] + "," + object[2]);
		}
		if(session != null) session.close();
	}
	
	/*
	 * 分页
	 */
	@Test
	public void test8() {
		String hql = "from User";
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.createQuery(hql);
		
		query.setFirstResult(1);//设置其实查询点
		query.setMaxResults(2);//设置查询的长度
		
		List<User> list = query.list();
		
		for (User object : list) {
			System.out.println(object.getUname() + "," + object.getAge());
		}
		
		if(session != null) session.close();
	}
	
	
}
